package tasks

import (
	"context"
	"database/sql"
	"fmt"
	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/shopspring/decimal"
	"jade/modules/lib"
	"log"
	"strconv"
	"strings"
	"time"
)

type RowData struct {
	CompanyID      string
	SupplierID     string
	ID             string
	Amount         string
	Cost           string
	RelatedType    string
	RelatedId      string
	CreateTime     string
	StatType       string
	OperationId    string
	Type           string
	ProjectId      string
	CompanyName    string
	SupplierName   string
	Name           string
	FullName       string
	Remark         string
	PayMethod      string
	BdName         string
	Bd             string
	DepartmentName string
	Key            string
}

type InsertData struct {
	Data []string
}

func Prepaid(dbConnections map[string]*sql.DB, month string, ch clickhouse.Conn, storage string) {
	// 判断是否为最后一天
	if !lib.IsLastDayOfMonth(time.Now()) {
		log.Printf("时间未到")
		return
	}

	start, end, month, err0 := lib.GetMonthStartAndEnd(month)
	log.Printf(fmt.Sprintf("%s:%s - %s 预付执行开始：Start……", month, start, end))
	//os.Exit(0)
	if err0 != nil {
		fmt.Println("Error:", err0)
		return
	}

	bfSql := `select 
fc.company_id,
fc.supplier_id,
fc.id,
fc.amount,
fc.cost,
fc.related_type,
fc.related_id,
fc.create_time,
fc.stat_type,
fc.operation_id, 
fc.type,
fc.project_id
from duomai_business_financial.finance_cost fc
where income_id = 0 and cost != 0
# AND project_id = 14
AND create_time BETWEEN '%s 00:00:00' AND '%s 23:59:59'
`
	businessFinancial := dbConnections["business_financial"]
	rows, err := businessFinancial.Query(fmt.Sprintf(bfSql, start, end))
	if err != nil {
		log.Printf("Error querying business_financial: %v", err)
		return
	}

	// 用于存储结果的切片
	var companyIds []string
	var supplierIds []string
	var relatedIds []string
	var operationIds []string

	// 存储结果
	var results []RowData

	for rows.Next() {
		var companyId, supplierId, id, amount, cost, relatedType, relatedId, createTime, statType, operationId, types, projectId, key string
		if err := rows.Scan(&companyId, &supplierId, &id, &amount, &cost, &relatedType, &relatedId, &createTime, &statType, &operationId, &types, &projectId); err != nil {
			log.Printf("Error scanning row: %v", err)
			continue
		}
		companyIds = append(companyIds, companyId)
		supplierIds = append(supplierIds, supplierId)
		relatedIds = append(relatedIds, relatedId)
		operationIds = append(operationIds, operationId)
		key = "i" + relatedId + "-p" + operationId //与crm匹配标记

		results = append(results, RowData{
			CompanyID:   companyId,
			SupplierID:  supplierId,
			ID:          id,
			Amount:      amount,
			Cost:        cost,
			RelatedType: relatedType,
			RelatedId:   relatedId,
			CreateTime:  createTime,
			StatType:    statType,
			OperationId: operationId,
			Type:        types,
			ProjectId:   projectId,
			Key:         key,
		})
	}

	//财务侧数据
	finance := dbConnections["finance"]
	companies, err1 := finance.Query(fmt.Sprintf("select id,company from oa_company where id IN (%s)", strings.Join(lib.RemoveDuplicates(companyIds, "-1"), ", ")))

	// 查询 companies 数据
	companyMap := make(map[string]string)
	if err1 != nil {
		log.Printf("Error querying companies: %v", err1)
	} else {
		for companies.Next() {
			var id, companyName string
			if err := companies.Scan(&id, &companyName); err != nil {
				log.Printf("Error scanning company: %v", err)
				continue
			}
			companyMap[id] = companyName
		}
	}

	// 查询 suppliers 数据
	suppliers, err2 := finance.Query(fmt.Sprintf("select id,supplier_name from oa_supplier where id IN (%s)", strings.Join(lib.RemoveDuplicates(supplierIds, "-1"), ", ")))
	supplierMap := make(map[string]string)
	if err2 != nil {
		log.Printf("Error querying supplier: %v", err2)
	} else {
		for suppliers.Next() {
			var id, supplierName string
			if err := suppliers.Scan(&id, &supplierName); err != nil {
				log.Printf("Error scanning supplier: %v", err)
				continue
			}
			supplierMap[id] = supplierName
		}
	}

	//项目中台
	crm := dbConnections["crm"]
	crmSql := "select pi.id, pi.name, pt.full_name, pp.id as ppid, pp.remark,pp.pay_method,pp.bd_name,pp.bd from duomai_crm.project_income pi left join duomai_crm.project_income ppi on pi.parent_id = ppi.id left join duomai_crm.project_type_related ptr on pi.id=ptr.related_id and ptr.type=2 left join duomai_crm.project_type pt on ptr.project_type_id = pt.id left join duomai_crm.project_payouts pp on pp.id in (%s) where pi.id IN (%s) and (ppi.rewards_time = 0 or ppi.rewards_time is null)"
	crmRows, err3 := crm.Query(fmt.Sprintf(crmSql, strings.Join(lib.RemoveDuplicates(operationIds, "-1"), ", "), strings.Join(lib.RemoveDuplicates(relatedIds, "-1"), ", ")))

	var bds []string
	crmMap := make(map[string][]string)
	if err3 != nil {
		log.Printf("Error querying crm: %v", err3)
	} else {
		for crmRows.Next() {
			var id, name, fullName, ppid, remark, payMethod, bdName, bd string
			if crmErr := crmRows.Scan(&id, &name, &fullName, &ppid, &remark, &payMethod, &bdName, &bd); crmErr != nil {
				log.Printf("Error scanning crmRow: %v", crmErr)
				continue
			}
			crmMap["i"+id+"-p"+ppid] = []string{name, fullName, remark, payMethod, bdName, bd}
			bds = append(bds, bd)
		}
	}

	// cps部分数据
	cpsRows, cpsErr := dbConnections["cps"].Query(fmt.Sprintf("SELECT u.id,d.`Name` AS 'department_name' FROM users u LEFT JOIN departments d ON u.department_id = d.DepartmentID WHERE u.id IN (%s)", strings.Join(lib.RemoveDuplicates(bds, "-1"), ", ")))
	cpsMap := make(map[string]string)
	if cpsErr != nil {
		log.Printf("Error querying cps: %v", cpsErr)
	} else {
		for cpsRows.Next() {
			var id, departmentName string
			if err := cpsRows.Scan(&id, &departmentName); err != nil {
				log.Printf("Error scanning cps: %v", err)
			}
			cpsMap[id] = departmentName
		}
	}

	// 遍历 rows 并合并 company 名称
	for i := 0; i < len(results); {
		results[i].CompanyName = companyMap[results[i].CompanyID]
		results[i].SupplierName = supplierMap[results[i].SupplierID]

		crmInfo := lib.GetOrDefault(crmMap, results[i].Key, []string{}).([]string)
		if len(crmInfo) == 0 {
			// 如果 crmInfo 为空，则移除当前元素
			results = append(results[:i], results[i+1:]...)
		} else {
			results[i].Name = lib.GetOrDefault(crmInfo, "0", "").(string)
			results[i].FullName = lib.GetOrDefault(crmInfo, "1", "").(string)
			results[i].Remark = lib.GetOrDefault(crmInfo, "2", "").(string)
			results[i].PayMethod = lib.GetOrDefault(crmInfo, "3", "0").(string)
			results[i].BdName = lib.GetOrDefault(crmInfo, "4", "").(string)
			results[i].Bd = lib.GetOrDefault(crmInfo, "5", "0").(string)
			results[i].DepartmentName = lib.GetOrDefault(cpsMap, results[i].Bd, "").(string)

			i++ // 仅在未删除元素时递增 i
		}
	}

	if storage == "clickhouse" {
		insertClickhouse(results, ch, month)
	} else {
		dk := dbConnections["doukuai"]
		insertMysql(results, dk, month)
	}
	log.Printf(fmt.Sprintf("%s预付执行完成：Done!", month))

	defer func(rows *sql.Rows) {
		err := rows.Close()
		if err != nil {
			return
		}
	}(rows)
}

func insertMysql(results []RowData, dk *sql.DB, month string) {
	//历史存在则删除
	_, err := dk.Exec(fmt.Sprintf("delete from prepaid_snapshot where month = '%s'", month))
	if err != nil {
		log.Printf("Error delete prepaid_snapshot history data: %v", err)
		return
	}

	// 遍历 rows 并合并 company 名称
	insertData := make([]string, len(results))
	for i, row := range results {
		insertData[i] = "('" +
			month + "'," +
			row.CompanyID + ",'" +
			row.CompanyName + "'," +
			row.SupplierID + ",'" +
			row.SupplierName + "'," +
			row.Amount + "," +
			row.Cost + "," +
			row.RelatedType + "," +
			row.RelatedId + ",'" +
			row.CreateTime + "'," +
			row.StatType + "," +
			row.OperationId + "," +
			row.Type + "," +
			row.ProjectId + ",'" +
			row.Name + "','" +
			row.FullName + "','" +
			row.Remark + "'," +
			row.PayMethod + ",'" +
			row.BdName + "'," +
			row.Bd + ",'" +
			row.DepartmentName +
			"')"
	}

	if len(insertData) == 0 {
		fmt.Println(fmt.Sprintf("%s无可写入数据", month))
		return
	}

	//快照表保存sql
	insertQuery := `INSERT INTO prepaid_snapshot (
  month,company_id,company_name,supplier_id,supplier_name,amount,cost,related_type,related_id,
  create_time,stat_type,operation_id,type,project_id,name,full_name,remark,pay_method,bd_name,bd,department_name
  ) VALUES %s`

	_, insertErr := dk.Exec(fmt.Sprintf(insertQuery, strings.Join(insertData, ",")))
	if insertErr != nil {
		log.Printf("Error inserting into prepaid_snapshot: %v", insertErr)
	}
}

// 插入到 ClickHouse
func insertClickhouse(results []RowData, ch clickhouse.Conn, month string) {
	ctx := context.Background()
	/*if err := ch.Ping(ctx); err != nil {
		log.Fatalf("Failed to ping ClickHouse: %v", err)
	}*/

	// 检查是否存在指定 month 的数据
	var exists bool
	query := "SELECT count(*) > 0 FROM prepaid_snapshot WHERE month = ?"
	if err := ch.QueryRow(ctx, query, month).Scan(&exists); err != nil {
		log.Fatalf("Failed to check existing data: %v", err)
	}

	// 如果存在，删除数据
	if exists {
		deleteQuery := "ALTER TABLE prepaid_snapshot DELETE WHERE month = ?"
		if err := ch.Exec(ctx, deleteQuery, month); err != nil {
			log.Fatalf("Failed to delete existing data: %v", err)
		}
	}

	//批量写入
	batch, err := ch.PrepareBatch(ctx, "INSERT INTO prepaid_snapshot (month,company_id,company_name,supplier_id,supplier_name,amount,cost,related_type,related_id,create_time,stat_type,operation_id,type,project_id,name,full_name,remark,pay_method,bd_name,bd,department_name)")
	if err != nil {
		log.Fatalf("Failed to prepare ClickHouse batch: %v", err)
	}

	for _, data := range results {
		companyId, _ := strconv.ParseUint(data.CompanyID, 10, 32)
		supplierId, _ := strconv.ParseUint(data.SupplierID, 10, 32)
		relatedId, _ := strconv.ParseUint(data.RelatedId, 10, 32)
		operationId, _ := strconv.ParseUint(data.OperationId, 10, 32)
		projectId, _ := strconv.ParseUint(data.ProjectId, 10, 32)
		bd, _ := strconv.ParseUint(data.Bd, 10, 32)

		relatedType, _ := strconv.ParseUint(data.RelatedType, 10, 8)
		statType, _ := strconv.ParseUint(data.StatType, 10, 8)
		typeId, _ := strconv.ParseUint(data.Type, 10, 8)
		payMethod, _ := strconv.ParseUint(data.PayMethod, 10, 8)

		amount, _ := decimal.NewFromString(data.Amount)
		cost, _ := decimal.NewFromString(data.Cost)

		if err := batch.Append(
			month, companyId, data.CompanyName, supplierId, data.SupplierName, amount, cost, relatedType, relatedId, data.CreateTime, statType,
			operationId, typeId, projectId, data.Name, data.FullName, data.Remark, payMethod, data.BdName, bd, data.DepartmentName); err != nil {
			log.Fatalf("Failed to append to batch: %v", err)
		}
	}

	if err := batch.Send(); err != nil {
		log.Fatalf("Failed to send batch: %v", err)
	}
}
